/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.x3.purchasing.operator;

import com.x3.purchasing.account.Account;
import com.x3.purchasing.common.ClassConnection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.springframework.jdbc.core.RowMapper;

/**
 *
 * @author kris
 */
public class OperatorDAOImpl implements OperatorDAO {

    public OperatorDAOImpl() {
        ClassConnection.getTransactionProxyFactoryBean().setTarget(this);
    }

    public void insert(Operator operator) throws Exception {
        String sql = "INSERT INTO operator(nama,telp,email,account_id) VALUES(?,?,?,?)";
        ClassConnection.getJdbc().update(sql, new Object[]{
                    operator.getNama(),
                    operator.getTelp(),
                    operator.getEmail(),
                    operator.getAccount().getId()
                });
    }

    public void update(Operator operator) throws Exception {
        String sql = "UPDATE operator SET nama=?, telp=?, email=? WHERE id=?";
        ClassConnection.getJdbc().update(sql, new Object[]{
                    operator.getNama(),
                    operator.getTelp(),
                    operator.getEmail(),
                    operator.getId()
                });
    }

    public void delete(int id) throws Exception {
        String sql = "DELETE FROM operator WHERE id=?";
        ClassConnection.getJdbc().update(sql, new Object[]{id});
    }

    public Operator get(int id) throws Exception {
        Operator operator = null;
        String sql = "SELECT o.id, o.nama, o.telp, o.email, a.id, a.username, a.password FROM operator o "
                + " INNER JOIN account a ON a.id = o.account_id WHERE o.id = ?";
        operator = (Operator) ClassConnection.getJdbc().queryForObject(sql, new Object[]{id}, new RowMapper() {

            public Object mapRow(ResultSet rs, int i) throws SQLException {
                Account account = new Account();
                account.setId(rs.getInt("a.id"));
                account.setUsername(rs.getString("a.username"));
                account.setPassword(rs.getString("a.password"));

                Operator temp = new Operator();
                temp.setId(rs.getInt("o.id"));
                temp.setNama(rs.getString("o.nama"));
                temp.setTelp(rs.getString("o.telp"));
                temp.setEmail(rs.getString("o.email"));
                temp.setAccount(account);

                return temp;
            }
        });
        return operator;
    }

    public List<Operator> gets() throws Exception {
        List<Operator> data = new ArrayList<Operator>();
        String sql = "SELECT o.id, o.nama, o.telp, o.email, a.id as user_id, a.username, a.password FROM operator o "
                + " INNER JOIN account a ON a.id = o.account_id";
        List<Map> rows = ClassConnection.getJdbc().queryForList(sql);
        for (Map map : rows) {
            Account account = new Account();
            account.setId(Integer.valueOf(map.get("user_id").toString()));
            account.setUsername(map.get("username").toString());
            account.setPassword(map.get("password").toString());

            Operator operator = new Operator();
            operator.setId(Integer.valueOf(map.get("id").toString()));
            operator.setNama(map.get("nama").toString());
            operator.setTelp(map.get("telp").toString());
            operator.setEmail(map.get("email").toString());
            operator.setAccount(account);
            data.add(operator);
        }
        return data;
    }

    public Operator getByAccount(int id) throws Exception {
        Operator operator = null;
        String sql = "SELECT o.id, o.nama, o.telp, o.email, "
                + " a.id as user_id, a.username, a.password FROM operator o "
                + " INNER JOIN account a ON a.id = o.account_id"
                + " WHERE a.id=?";
        operator = (Operator) ClassConnection.getJdbc().queryForObject(sql, new Object[]{id}, new RowMapper() {

            public Object mapRow(ResultSet rs, int i) throws SQLException {
                Account account = new Account();
                account.setId(rs.getInt("user_id"));
                account.setUsername(rs.getString("username"));
                account.setPassword(rs.getString("password"));

                Operator operator = new Operator();
                operator.setId(rs.getInt("id"));
                operator.setNama(rs.getString("nama"));
                operator.setTelp(rs.getString("telp"));
                operator.setEmail(rs.getString("email"));
                operator.setAccount(account);

                return operator;
            }
        });
        return operator;
    }

    public Operator getOperatorByOrder(int orderDetailId) throws Exception {
        Operator operator = null;
        String sql = "SELECT  op.id,op.nama,op.telp,op.email FROM orders o "
                + " INNER JOIN operator op ON op.id =o.operator_id "
                + " INNER JOIN detail_order d ON d.order_id=o.id WHERE d.id = ?";
        operator = (Operator) ClassConnection.getJdbc().queryForObject(sql, new Object[]{orderDetailId}, new RowMapper() {

            public Object mapRow(ResultSet rs, int i) throws SQLException {
                Operator operator = new Operator();
                operator.setId(rs.getInt("id"));
                operator.setNama(rs.getString("nama"));
                operator.setTelp(rs.getString("telp"));
                operator.setEmail(rs.getString("email"));

                return operator;
            }
        });
        return operator;
    }
}
